package Serveur;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;

public class DB {
	private static String url = "jdbc:mysql://127.0.0.1:3306/twitter";
	private static String username = "root";
	private static String password = "";
	private static Connection con = null;
	
	public static void createCon() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static boolean authentificate(String email, String password) {		
		if(con == null) {
			createCon();
		}
		try {
			String maRequete = "SELECT * FROM utilisateur WHERE email='"+email+"' AND password='"+password+"';";
			
			//String maRequete = "SELECT * FROM utilisateur WHERE email=? AND password=?;";
			
			PreparedStatement preparedStatement = con.prepareStatement(maRequete);
			//preparedStatement.setString(1, email);
			//preparedStatement.setString(2, password);
			
			ResultSet resultat = preparedStatement.executeQuery(maRequete);

		    if(resultat.first()) {
		    	return true;
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public static void saveTweet(String email, String message) {
		if(con == null)
			createCon();
		try {
			String maRequete = "INSERT INTO tweet " + 
			"VALUES(null, (SELECT id FROM utilisateur WHERE email = '"+email+"'),  '"+message+"', null);";
			
			//String maRequete = "INSERT INTO tweet " + 
			//		"VALUES(null, (SELECT id FROM utilisateur WHERE email =?), ?, null);";
			
			PreparedStatement preparedStatement = con.prepareStatement(maRequete);
			//preparedStatement.setString(1, email);
			//preparedStatement.setString(2, message);
			
			preparedStatement.executeQuery(maRequete);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static HashSet<HashSet<String>> getTweets(String email) {
		HashSet<HashSet<String>> tweets = new HashSet<HashSet<String>>();
		if(con == null) {
			createCon();
		}
		try {
			String maRequete = "SELECT auteur.email, tweet.message " +
				"FROM tweet, utilisateur, utilisateur as auteur, follower " +
				"WHERE utilisateur.email = '"+email+"' " +
				"AND utilisateur.id = follower.idUtilisateur " +
				"AND tweet.auteur = follower.idFollower " +
				"AND auteur.id = tweet.auteur " +
				"ORDER BY tweet.timestamp DESC;";
			
			//String maRequete = "SELECT auteur.email, tweet.message " +
			//		"FROM tweet, utilisateur, utilisateur as auteur, follower " +
			//		"WHERE utilisateur.email = ? " +
			//		"AND utilisateur.id = follower.idUtilisateur " +
			//		"AND tweet.auteur = follower.idFollower " +
			//		"AND auteur.id = tweet.auteur " +
			//		"ORDER BY tweet.timestamp DESC;";
			
			PreparedStatement preparedStatement = con.prepareStatement(maRequete);
			//preparedStatement.setString(1, email);
			ResultSet resultat = preparedStatement.executeQuery(maRequete);

		    while(resultat.next()) {
		    	HashSet<String> couple = new HashSet<String>();
		    	couple.add(resultat.getString("email"));
		    	couple.add(resultat.getString("message"));
		    	tweets.add(couple);
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tweets;
	}
	
	public static HashSet<String> getFollowers(String email) {
		HashSet<String> followers = new HashSet<String>();
		if(con == null) {
			createCon();
		}
		try {
			String maRequete = 
					"SELECT follow.email FROM utilisateur, utilisateur as follow, follower " +
					"WHERE utilisateur.id = follower.idUtilisateur " + 
					"AND follow.id = follower.idFollower " +
					"AND utilisateur.email = '"+email+"';";
		
			//String maRequete = 
			//		"SELECT follow.email FROM utilisateur, utilisateur as follow, follower " +
			//		"WHERE utilisateur.id = follower.idUtilisateur " + 
			//		"AND follow.id = follower.idFollower " +
			//		"AND utilisateur.email =?;";
			
			PreparedStatement preparedStatement = con.prepareStatement(maRequete);
			//preparedStatement.setString(1, email);
			ResultSet resultat = preparedStatement.executeQuery(maRequete);

		    while(resultat.next()) {
		    	followers.add(resultat.getString("email"));
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return followers;
	}
}
